CREATE PROCEDURE [dbo].a_Articles_Img_selImgMainByArticle
	@ArtID int
AS

SELECT
	[IID],
	[AID],
	[ImageNo],
	[ImageW],
	[Title],
	[CountryID],
	[FYear],
	[SeasonID],
	[WTypeID],
	[SexID],
	[IsMainImg],
	[Keywords],
	[ImgOrientation],
	[LayoutFormat],
	[StatPopularity],
	[RatingCount],
	[RatingAvg],
	[ZoomCount]
FROM
	[dbo].[a_Articles_Img]
WHERE
	[AID] = @ArtID AND [IsMainImg] = 0
ORDER BY RatingCount DESC;
/*
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
*/

CREATE PROCEDURE [dbo].a_Articles_Img_selListByAID
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@AID int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #Tempa_Articles_Img (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	IID int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #Tempa_Articles_Img ([IID]) SELECT '
SET @sql = @sql + ' [IID] FROM [dbo].[a_Articles_Img] WHERE [AID]=' + CAST(@AID AS VARCHAR(10)) + N' AND IsMainImg=0 ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [a_Articles_Img] WHERE [AID]=@AID AND IsMainImg=0

SELECT
	[dbo].[a_Articles_Img].[IID],
	[AID],
	[ImageNo],
	[ImageW],
	[Title],
	[CountryID],
	[FYear],
	[SeasonID],
	[WTypeID],
	[SexID],
	[IsMainImg],
	[Keywords],
	[ImgOrientation],
	[LayoutFormat],
	[StatPopularity],
	[RatingCount],
	[RatingAvg],
	[ZoomCount]
FROM
	#Tempa_Articles_Img AS tblTemp JOIN [dbo].[a_Articles_Img] ON
	tblTemp.IID = [dbo].[a_Articles_Img].IID 	
WHERE ((@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)) AND [AID]=@AID AND IsMainImg=0
ORDER BY RowNumber

DROP TABLE #Tempa_Articles_Img
	